﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using CvModel;
using System.Data.Entity.Migrations;
using System.Data.SqlClient;
using System.Data;

namespace DAL
{
    public class CvDB
    {
        private static CvModelContainer _context = new CvModelContainer();

        //CREATE
        public static int CreateCV(Cv cv)
        {
            try
            {
                var query = _context.Cv.Add(cv);
                return _context.SaveChanges();
            }
            catch (Exception)
            {

                throw;
            }
        }

        public static int CreateMerit(Merits merit)
        {
            try
            {
                var query = _context.Merits.Add(merit);
                return _context.SaveChanges();
            }
            catch (Exception)
            {

                throw;
            }
        }
        public static int CreateExperiences(Experiences experience)
        {
            try
            {
                var query = _context.Experiences.Add(experience);
                return _context.SaveChanges();
            }
            catch (Exception)
            {

                throw;
            }
        }

        public static int CreateEducations(Educations education)
        {
            try
            {
                var query = _context.Educations.Add(education);
                return _context.SaveChanges();
            }
            catch (Exception)
            {
                
                throw;
            }
        }

        //READ
        public static Cv GetCv(int cvId)
        {
            try
            {
                return _context.Cv.Where(c => c.CvId == cvId).ToList().First();
            }
            catch (Exception)
            {

                throw;
            }
        }

        public static int GetCvIdWithApplicantId(int applicantId)
        {
            try
            {
                var query =
                    (from c in _context.Cv
                     where c.ApplicantApplicantId == applicantId
                     select c).ToList().First();
                return query.CvId;
            }
            catch (Exception)
            {

                throw;
            }
        }

        public static Cv GetCvInstanceWithApplicantId(int applicantId)
        {
            try
            {
                var query =
                    (from c in _context.Cv
                     where c.ApplicantApplicantId == applicantId
                     select c).SingleOrDefault();
                return query;
            }
            catch (Exception)
            {

                throw;
            }
        }

        public static int GetLatestCVId()
        {
            try
            {
                var query =
                    (from c in _context.Cv
                    orderby c.CvId descending
                    select c).First();

                return query.CvId;
            }
            catch (Exception)
            {
                
                throw;
            }
        }
        //UPDATE
        public static int UpdateCV(Cv cv)
        {
            try
            {
                _context.Cv.AddOrUpdate(cv);
                return _context.SaveChanges();
            }
            catch (Exception)
            {

                throw;
            }
        }

        public static int UpdateEducation(Educations education)
        {
            try
            {
                _context.Educations.AddOrUpdate(education);
                return _context.SaveChanges();
            }
            catch (Exception)
            {
                
                throw;
            }
        }

        public static int UpdateExperience(Experiences experience)
        {
            try
            {
                _context.Experiences.AddOrUpdate(experience);
                return _context.SaveChanges();
            }
            catch (Exception)
            {
                
                throw;
            }
        }

        public static int UpdateMerits(Merits merit)
        {

            try
            {
                _context.Merits.AddOrUpdate(merit);
                return _context.SaveChanges();
            }
            catch (Exception)
            {
                
                throw;
            }
        }
        //DELETE
        public static int DeleteEducation(int educationId)
        {
            try
            {
                var query = _context.Educations.SingleOrDefault(e => e.EducationId == educationId);
                _context.Educations.Remove(query);
                return _context.SaveChanges();
            }
            catch
            {
                throw;
            }
        }
        public static int DeleteMerit(int meritId)
        {
            try
            {
                var query = _context.Merits.SingleOrDefault(e => e.MeritId == meritId);
                _context.Merits.Remove(query);
                return _context.SaveChanges();
            }
            catch (Exception)
            {
                throw;
            }
        }
        public static int DeleteExperience(int experienceId)
        {
            try
            {
                var query = _context.Experiences.SingleOrDefault(e => e.ExperienceId == experienceId);
                _context.Experiences.Remove(query);
                return _context.SaveChanges();
            }
            catch (Exception)


            {
                throw;
            }
        }
        
        public static int DeleteCV(int cvId)
        {
            try
            {
                var query =
                    (from c in _context.Cv
                     where c.CvId == cvId
                     select c).Single();

                _context.Cv.Remove(query);

                return _context.SaveChanges();
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}
